package com.dao;

import com.entity.IdCard;
import com.entity.Students;
import org.apache.ibatis.annotations.*;
import java.util.List;

public interface StudentsMapper {

    @Select("select * from students where sid=#{sid}")
    @Results(@Result(column = "cid",property = "card",javaType = IdCard.class, one = @One(select = "com.dao.IdCardMapper.getIdCardById")))
    public Students getStudentBySid(Integer sid);


    @Select("select s.*,c.code from students s,idcard c where s.cid = c.id and s.sid=#{sid}")
    @Results({
            @Result(property = "card.id",column = "cid" ),
            @Result(property = "card.code",column = "code")
    })
    public Students getStudentBySid2(Integer sid);

    //根据班级编号查询学生集合
    @Select("select * from students where class_room_id=#{classRoomId}")
    public List<Students> getStudentsByClassRoomId(Integer classRoomId);

    //根据学生编号查询带班级信息的学生资料
    @Select("select s.*,c.code,cr.cname from students s,idcard c,classroom cr where s.cid = c.id and s.class_room_id=cr.cid and s.sid=#{sid}")
    @Results({
            @Result(property = "card.id",column = "cid" ),
            @Result(property = "card.code",column = "code"),
            @Result(property = "classRoom.cid",column = "class_room_id"),
            @Result(property = "classRoom.cname",column = "cname")
    })
    public Students getStudentWithClassRoomBySid(Integer sid);

    //根据学生编号查询带教师集合信息的学生信息
    @Select("select * from students where sid = #{sid}")
    @Results({
            @Result(id=true,property = "sid",column = "sid"),
            @Result(property = "teachers",column = "sid",many=@Many(select = "com.dao.TeachersMapper.queryTeachersFromStudentsAndTeachersBySid"))
    })
    public Students getStudentWithTeachersBySid(Integer sid);

    //从中间表中根据教师编号查询学生集合
    @Select("select * from students where sid in (select sid from students_teachers where tid=#{tid})")
    public List<Students> getStudentsFromStudentsAndTeachersByTid(Integer tid);
}
